# -*- coding: utf-8 -*-
from sqlite3    import dbapi2 as sqlite3
from werkzeug   import check_password_hash, generate_password_hash
# configuration
DATABASE = 'teampupshoppers.db'
import json
from datetime import date
def connect_db():
    """Returns a new connection to the database."""
    return sqlite3.connect(DATABASE)

def clear_db():
    try:
        con = connect_db()
        cur = con.cursor()
        cur.execute("DELETE FROM user", [])
        cur.execute("DELETE FROM notice", [])
        cur.execute("DELETE FROM notice_user", [])
        con.commit()
        
    except sqlite3.Error, e:
        if con:
            con.rollback()
    finally:
        if con:
            con.close()    
   
def init_db():
    sql_insert_user = "INSERT INTO user (name, email, pw_hash ) VALUES (?, ?, ?)"
    sql_insert_notice = "INSERT INTO notice (name,shop_name,descr,planned_day_of_order,date_create ) VALUES (?,?,?,?,?)"
    sql_insert_notice_user = "INSERT INTO notice_user (notice_id, user_id ) VALUES (?,?)"

    try:
        con = connect_db()
        cur = con.cursor()
        cur.execute(sql_insert_user, ["test1", "test1@test.com", generate_password_hash("test1")])
        user_id = cur.lastrowid
        cur.execute(sql_insert_notice, ["test_notice_1",
                                        "test_shop_name1",
                                        "test_descr1",
                                        date.today(),
                                        date.today()])
        notice_id = cur.lastrowid      
        cur.execute(sql_insert_notice_user, [notice_id,user_id])
        con.commit()
        
        cur.execute(sql_insert_notice, ["test_notice_2",
                                        "test_shop_name2",
                                        "test_descr2",
                                        date.today(),
                                        date.today()])
        notice_id = cur.lastrowid      
        cur.execute(sql_insert_notice_user, [notice_id,user_id])
        con.commit()
        
    except sqlite3.Error, e:
        if con:
            con.rollback()
    finally:
        if con:
            con.close()    

def get_data():
    sql_select_user = "  SELECT   user.id, user.name, user.email " \
                        "FROM     user "
                        
    sql_select_notice = "SELECT   notice.id, notice.name, notice.shop_name, notice.descr, notice.planned_day_of_order " \
                        "FROM     notice, notice_user " \
                        "WHERE    notice_user.user_id = ?" \
                        "AND      notice.id = notice_user.notice_id"
                        
    try:
        con = connect_db()
        
        cur = con.execute(sql_select_user, [])
        for row in cur.fetchall():
            feedback = dict( (cur.description[idx][0], value)
                            for idx, value in enumerate(row))
            cur1 = con.execute(sql_select_notice, [feedback["id"]])
            
            rv = [dict(
                (cur1.description[idx][0], value)
                   for idx, value in enumerate(row)
                 )
                for row in cur1.fetchall()]
            print rv
            print json.dumps(rv)
        
    except sqlite3.Error, e:
        if con:
            con.rollback()
    finally:
        if con:
            con.close()    
    
if __name__ == '__main__':
    clear_db()
    init_db()
    get_data()